Data Loading & Initial Exploration¶

In [1]:
import pandas as pd

# Read GBP LIBOR historical data
df = pd.read_csv("LIBOR GBP.csv")
df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 9600 entries, 0 to 9599
Data columns (total 9 columns):
 #   Column    Non-Null Count  Dtype  
---  ------    --------------  -----  
 0   Date      9600 non-null   object 
 1   Week day  9600 non-null   object 
 2   ON        5307 non-null   float64
 3   1W        6083 non-null   float64
 4   1M        9409 non-null   float64
 5   2M        8842 non-null   float64
 6   3M        9600 non-null   float64
 7   6M        9409 non-null   float64
 8   12M       9095 non-null   float64
dtypes: float64(7), object(2)
memory usage: 675.1+ KB
  • Source of Data: The GBP LIBOR historical data was sourced from http://iborate.com/gbp-libor/.
  • It's important to note that the dataset has varying amounts of missing values ('nulls') across different maturity periods.
  • For our analysis, we'll focus on interest rates with 3-month and 6-month maturities, as they have the least amount of null values.
In [2]:
# Extract data where both '3M' and '6M' columns are not null
df_libor = df[df['3M'].notna() & df['6M'].notna()]

# Include only the maturities we want
df_libor = df_libor[['Date', '3M', '6M']].reset_index(drop=True)

# Reformat the 'Date' column to a standard date format
df_libor['Date'] = pd.to_datetime(df_libor['Date'], format='%d.%m.%Y')
df_libor.set_index('Date', inplace=True)
df_libor.head()
Out[2]:
3M 6M
Date
2023-03-31 4.4180 4.7447
2023-03-30 4.4129 4.7189
2023-03-29 4.4042 4.7094
2023-03-28 4.4119 4.7307
2023-03-27 4.3888 4.6853
  • Now the data contains no null values and is properly formatted.
  • Before proceeding, let's visualise the historical evolution of GBP LIBOR interest rates over time.
In [3]:
import plotly.express as px

# Create a line plot using Plotly
fig = px.line(df_libor, x=df_libor.index, y=['3M', '6M'],
              labels={'Date': 'Date', 'value': 'Interest Rates (%)'},
              title='Historical Trend of 3-Month and 6-Month GBP LIBOR Rates',
              color_discrete_sequence=["dodgerblue", "orange"])

# Customize the appearance
fig.update_traces(mode='lines', line=dict(width=2))
fig.update_layout(showlegend=True, legend_title_text='Maturity')
fig.update_yaxes(title_text='Interest Rates (%)')

# Show the interactive plot
fig.show()
  • As anticipated, the two interest rates closely follow one another.
  • Since 1990, there is a general downward trend in both interest rates, with a particularly sharp decline occurring in 2008 during the financial crisis.

Interest Rate Swap Demonstration & Net Cash Flow Visualization¶

In [4]:
# Set time parameters for the interest-rate swap (IRS)
start_date = "2000-01-01" # Start of a quarter
tenure = 4 # Tenure (Years)

# Set other parameters for IRS 
notional_amt = 100000 # Notional amount (GBP)
fixed_rate = 7 # Fixed-interest rate (%)
spread = 2 # Spread added to LIBOR (%)
  • Defining Interest Rate Swap (IRS) Parameters: These values, including the start date, tenure, notional amount, fixed interest rate, and LIBOR spread, will be adjustable in the Streamlit application.
In [5]:
# Aggregate interest-rate data to quarterly intervals
df_libor_quarterly = df_libor.resample('Q').mean()

# Create a DataFrame to store cash flows, using same quarterly intervals
df_cash_flow = pd.DataFrame({'Date': df_libor_quarterly.index})
df_cash_flow.set_index('Date', inplace=True)

# Add LIBOR interest rate as a column
df_cash_flow['libor_3m'] = df_libor_quarterly['3M']

# Calculate floating payments & fixed payments for each quarter
df_cash_flow["floating_payment"] = (notional_amt * (df_cash_flow['libor_3m'] + spread) / 100) * (1/4)
df_cash_flow["fixed_payment"] = (notional_amt * fixed_rate / 100) * (1/4)
df_cash_flow["net_cash_flow"] = df_cash_flow["fixed_payment"] - df_cash_flow["floating_payment"]

# Calculate the end date of the contract period
end_date = pd.to_datetime(start_date) + pd.DateOffset(years=tenure)

# Create a subset of payments during contract period (from start_date to end_date)
df_contract_cash_flows = df_cash_flow[(df_cash_flow.index >= start_date) & (df_cash_flow.index <= end_date)]
df_contract_cash_flows.head(5)
Out[5]:
libor_3m floating_payment fixed_payment net_cash_flow
Date
2000-03-31 6.203612 2050.902930 1750.0 -300.902930
2000-06-30 6.273138 2068.284590 1750.0 -318.284590
2000-09-30 6.205623 2051.405742 1750.0 -301.405742
2000-12-31 6.076402 2019.100437 1750.0 -269.100437
2001-03-31 5.715821 1928.955312 1750.0 -178.955312
  • Fixed and floating payments have been calculated, assuming quarterly payments*.
  • Net cash flow (fixed_payment - floating_payment) has also been added.
  • Also only payments during the contract period have been included.

Visualizations¶

In [6]:
# Create a subset of interest-rates during contract period (from start_date to end_date)
df_contract_libor_quarterly = df_libor_quarterly[(df_libor_quarterly.index >= start_date) & (df_libor_quarterly.index <= end_date)]

# Create a line plot using Plotly
fig = px.line(df_contract_libor_quarterly, x=df_contract_libor_quarterly.index, y='3M',
              title='GBP LIBOR 3-Month Rate Trends During Swap Contract')

# Customize the appearance
fig.update_traces(mode='lines', line=dict(width=3))
fig.update_yaxes(title_text='Interest Rate (%)')

# Show the interactive plot
fig.show()
In [7]:
# Create a line plot for net cash flow using Plotly
fig = px.line(df_contract_cash_flows, x=df_contract_cash_flows.index, y=['floating_payment', 'fixed_payment'],
              title='Timeline of Fixed vs. Floating Payments<br><sup>Assuming Quarterly Payment Intervals</sup>',
              color_discrete_sequence=["dodgerblue", "orange"])

# Customize the appearance
fig.update_traces(mode='lines', line=dict(width=3))
fig.update_layout(showlegend=True, legend_title_text='Payment Type')
fig.update_yaxes(title_text='Payment (£)')

# Show the interactive plot
fig.show()
  • Plot to show how fixed and floating payments change over time.
  • This graph illustrates the fluctuating nature of fixed and floating payments over the duration of the swap contract.
In [8]:
import plotly.graph_objs as go

# Split the data into positive and negative series
positive_cash_flow = df_contract_cash_flows['net_cash_flow'].apply(lambda x: x if x > 0 else None)
negative_cash_flow = df_contract_cash_flows['net_cash_flow'].apply(lambda x: x if x < 0 else None)

# Create the line plot
fig = go.Figure()

# Trace for positive cash flow
fig.add_trace(go.Scatter(x=df_contract_cash_flows.index, y=positive_cash_flow, 
                         mode='lines', line=dict(width=3, color='green'), 
                         fill='tozeroy', fillcolor='rgba(0, 255, 0, 0.5)', name='Positive Cash Flow'))

# Trace for negative cash flow
fig.add_trace(go.Scatter(x=df_contract_cash_flows.index, y=negative_cash_flow, 
                         mode='lines', line=dict(width=3, color='red'), 
                         fill='tozeroy', fillcolor='rgba(255, 0, 0, 0.5)', name='Negative Cash Flow'))

# Add a horizontal line at zero
fig.add_shape(type="line", x0=df_contract_cash_flows.index.min(), x1=df_contract_cash_flows.index.max(),
              y0=0, y1=0, line=dict(color="darkgrey", width=3))

# Customize the layout
fig.update_layout(title='Timeline of Net Cash Flow<br><sup>Net Cash Flow = Fixed Payment - Floating Payment</sup>',
                  yaxis_title='Cash Flow (£)')

# Show the plot
fig.show()
  • Positive Cash Flow: This occurs when the company paying a fixed interest rate (e.g., Company A) profits. It happens when the floating interest rate, which this company receives, is higher than the fixed rate it pays. In such cases, Company A earns more from the floating rate than its fixed-rate payments, resulting in a net gain or positive cash flow.

  • Negative Cash Flow: This happens when the floating interest rate falls below the fixed rate. In this scenario, Company A, which is committed to a fixed interest rate, ends up paying more than it receives from the floating rate. This mismatch leads to a financial loss, or negative cash flow.